package com.zhxh.nzpos.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.zhxh.nzpos.db.DBHelper;
import com.zhxh.nzpos.model.Buyin;
import com.zhxh.nzpos.model.Order;
import com.zhxh.nzpos.model.Stock;
import com.zhxh.nzpos.utils.RandomStringUtils;

/**
 * Buyin DAO
 * 
 * @author Administrator
 * 
 */
public class BuyinDao {

	DBHelper dbHelper;

	private final static String LOG_TAG = "BuyinDao";
	protected static final String TBL_NAME = "buyin";

	public BuyinDao(Context context) {
		dbHelper = new DBHelper(context);
	}

	/**
	 * add new buyin
	 * 
	 * @param buyin
	 */
	public String save(Buyin buyin) {
		Log.i(LOG_TAG, "BuyinDao  save " + buyin.toString());
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		buyin.setId(RandomStringUtils.randomNumeric(10));
		long time = System.currentTimeMillis();
		buyin.setCreated(time);
		buyin.setModified(time);

		db.beginTransaction();// 开始事务
		try {
			db.execSQL(
					"insert into buyin(ID,GoodID,Name,Buyindate,Price,Count,Ispayed,IsReceived,SupplierID,OpratorID,modified) values(?,?,?,?,?,?,?,?,?,?)",
					new Object[] { buyin.getId(), buyin.getGoodId(), buyin.getName(),
							buyin.getBuyinDate(), buyin.getPrice(),
							buyin.getCount(), buyin.isPayed(),
							buyin.isReceived(), buyin.getSupplierId(),
							buyin.getOpratorId(),
							buyin.getModified() });

			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("BuyinDao  save", e.getMessage());
		}
		db.endTransaction();// 事务结束;
		db.close();
		return buyin.getId();
	}
	
	/**
	 * save all new buyin
	 * 
	 * @param buyins
	 */
	public boolean saveAllAndOrder(ArrayList<Buyin> buyins, Order order) {
		Log.i(LOG_TAG, "BuyinDao  save all");
		boolean result = true;
		SQLiteDatabase db = dbHelper.getWritableDatabase();
//		db.beginTransaction();// 开始事务
		
		try {
			for (Buyin buyin : buyins) {
				buyin.setId(RandomStringUtils.randomNumeric(10));
				long time = System.currentTimeMillis();
				buyin.setCreated(time);
				buyin.setModified(time);
				db.execSQL(
						"insert into buyin(ID,GoodID,Name,Buyindate,Price,Price1,Count,Ispayed,IsReceived,SupplierID,OpratorID,created,modified,OrderId) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
						new Object[] { buyin.getId(), buyin.getGoodId(),
								buyin.getName(), buyin.getBuyinDate(),
								buyin.getPrice(), buyin.getPrice(),
								buyin.getCount(), buyin.isPayed(),
								buyin.isReceived(), buyin.getSupplierId(),
								buyin.getOpratorId(), buyin.getCreated(),
								buyin.getModified(),order.getId() });

			}
			db.execSQL(
					"insert into buyin_order(ID,OrderNumber,TotalCount,HasPayed) values(?,?,?,?)",
					new Object[] { order.getId(), order.getOrderNumber(),
							order.getTotalCount(), order.getHasPayed() });
		} catch (Exception e) {
			Log.i("BuyinDao  save all", e.getMessage());
			result = false;
		} finally {
			db.close();
		}
//		db.setTransactionSuccessful();
//		db.endTransaction();// 事务结束;
		
		return result;
	}

	/**
	 * update a buyin
	 * 
	 * @param buyin
	 */

	public void update(Buyin buyin) {
		Log.i(LOG_TAG, "BuyinDao  update " + buyin.toString());
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		buyin.setModified(System.currentTimeMillis());
		db.beginTransaction();// 开始事务
		try {
			db.execSQL(
					"update buyin set GoodID=?,Name=?,Buyindate=?,Price=?,Count=?,Ispayed=?,IsReceived=?,SupplierID=?,OpratorID=?,modified=? where ID=?",
					new Object[] { buyin.getGoodId(), buyin.getName(), buyin.getBuyinDate(),
							buyin.getPrice(), buyin.getCount(),
							buyin.isPayed(), buyin.isReceived(),
							buyin.getSupplierId(), buyin.getOpratorId(),
							buyin.getModified(), buyin.getId() });

			 db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("BuyinDao  update", e.getMessage());
		}
		db.endTransaction();// 事务结束;
		db.close();
	}
	
	public boolean confirmBuyins (ArrayList<Buyin> buyins, String date) {
		boolean result = true;
		Log.i(LOG_TAG, "Confirm buyins ");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
//		db.beginTransaction();// 开始事务
		try {
			for (Buyin buyin : buyins) {
				buyin.setReceived(true);
				buyin.setModified(System.currentTimeMillis());
				Stock stock = new Stock();
				stock.setId(RandomStringUtils.randomNumeric(10));
				stock.setCount(buyin.getCount());
				stock.setStockDate(date);
				stock.setLife(date);
				stock.setGoodsName(buyin.getName());
				stock.setGoodId(buyin.getGoodId());
				long time = System.currentTimeMillis();
				stock.setCreated(time);
				stock.setModified(time);
				db.execSQL(
						"update buyin set GoodID=?,Name=?,Buyindate=?,Price=?,Count=?,Ispayed=?,IsReceived=?,SupplierID=?,OpratorID=?,modified=? where ID=?",
						new Object[] { buyin.getGoodId(), buyin.getName(),
								buyin.getBuyinDate(), buyin.getPrice(),
								buyin.getCount(), buyin.isPayed(),
								buyin.isReceived(), buyin.getSupplierId(),
								buyin.getOpratorId(), buyin.getModified(),
								buyin.getId() });
				db.execSQL(
						"insert into stock(ID,GoodID,GoodsName,Count,Stockdate,Life,created,modified) values(?,?,?,?,?,?,?,?)",
						new Object[] { stock.getId(), stock.getGoodId(),
								stock.getGoodsName(), stock.getCount(),
								stock.getStockDate(), stock.getLife(),
								stock.getCreated(), stock.getModified() });

			}
		} catch (Exception e) {
			result = false;
			Log.i("Confirm Buyins", e.getMessage());
		} finally {
			db.close();
		}
//		db.setTransactionSuccessful();
//		db.endTransaction();// 事务结束;
		return result;
	}

	
	/**
	 * get a buyin by id
	 * 
	 * @param i
	 * @return
	 */
	public Buyin find(String id) {
		Log.i(LOG_TAG, "BuyinDao  find " + id);
		Buyin buyin = null;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID,GoodID,Name,Buyindate,Price,Count,Ispayed,IsReceived,SupplierID,OpratorID,modified from buyin where ID=?",
						new String[] { id });
		if (cursor.moveToNext()) {
			buyin = new Buyin();

			buyin.setId(cursor.getString(cursor.getColumnIndex("ID")));
			buyin.setGoodId(cursor.getString(1));
			buyin.setName(cursor.getString(2));
			buyin.setBuyinDate(cursor.getString(3));
			buyin.setPrice(cursor.getFloat(4));
			buyin.setCount(cursor.getInt(5));
			buyin.setPayed(Boolean.getBoolean(cursor.getString(6)));
			buyin.setReceived(Boolean.getBoolean(cursor.getString(7)));
			buyin.setSupplierId(cursor.getString(8));
			buyin.setOpratorId(cursor.getString(9));
			buyin.setModified(cursor.getLong(10));
		}
		cursor.close();
		db.close();
		return buyin;
	}
	/**
	 * get buyins by good id and date
	 * 
	 * 返回需要收货确认的订单
	 * @param i
	 * @return
	 */
	public ArrayList<Buyin> findByGoodIdAndDate(String goodId, String from, String end) {
		Log.i(LOG_TAG, "BuyinDao  find by goodId:" + goodId);
		ArrayList<Buyin> buyins = new ArrayList<Buyin>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select buyin.ID,buyin.GoodID,buyin.Name,buyin.Buyindate,goods.Price1,buyin.Count,buyin.Ispayed,buyin.IsReceived,buyin.SupplierID,buyin.OpratorID,buyin.modified from buyin,goods where buyin.GoodID=goods.ID and goods.ID=? and buyin.IsReceived=0 and buyin.Buyindate>=? and buyin.Buyindate<=? order by buyin.modified desc",
						new String[] { goodId, from, end });
		while (cursor.moveToNext()) {
			Buyin buyin = new Buyin();
			
			buyin.setId(cursor.getString(cursor.getColumnIndex("ID")));
			buyin.setGoodId(cursor.getString(1));
			buyin.setName(cursor.getString(2));
			buyin.setBuyinDate(cursor.getString(3));
			buyin.setPrice(cursor.getFloat(4));
			buyin.setCount(cursor.getInt(5));
			buyin.setPayed(Boolean.getBoolean(cursor.getString(6)));
			buyin.setReceived(Boolean.getBoolean(cursor.getString(7)));
			buyin.setSupplierId(cursor.getString(8));
			buyin.setOpratorId(cursor.getString(9));
			buyin.setModified(cursor.getLong(10));
			buyins.add(buyin);
		}
		cursor.close();
		db.close();
		return buyins;
	}
	
	/**
	 * get all buyin by date
	 * 
	 * 返回需要收货确认的订单
	 * @param i
	 * @return
	 */
	public ArrayList<Buyin> findByDate(String from, String end) {
		Log.i(LOG_TAG, "BuyinDao  find all by date between:"+from+" to "+end);
		ArrayList<Buyin> buyins = new ArrayList<Buyin>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select buyin.ID,buyin.GoodID,buyin.Name,buyin.Buyindate,goods.Price1,buyin.Count,buyin.Ispayed,buyin.IsReceived,buyin.SupplierID,buyin.OpratorID,buyin.modified from buyin,goods where buyin.GoodID=goods.ID and buyin.IsReceived=0 and buyin.Buyindate>=? and buyin.Buyindate<=? order by buyin.modified desc",
						new String[] { from, end });
		while (cursor.moveToNext()) {
			Buyin buyin = new Buyin();
			buyin.setId(cursor.getString(cursor.getColumnIndex("ID")));
			buyin.setGoodId(cursor.getString(1));
			buyin.setName(cursor.getString(2));
			buyin.setBuyinDate(cursor.getString(3));
			buyin.setPrice(cursor.getFloat(4));
			buyin.setCount(cursor.getInt(5));
			buyin.setPayed(Boolean.getBoolean(cursor.getString(6)));
			buyin.setReceived(Boolean.getBoolean(cursor.getString(7)));
			buyin.setSupplierId(cursor.getString(8));
			buyin.setOpratorId(cursor.getString(9));
			buyin.setModified(cursor.getLong(10));
			buyins.add(buyin);
		}
		cursor.close();
		db.close();
		return buyins;
	}

	/**
	 * delete a buyin by id
	 * 
	 * @param id
	 */
	public void delete(String id) {
		Log.i(LOG_TAG, "BuyinDao  delete " + id);
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete from buyin where ID=?", new Object[] { id });
		db.close();
	}

	/**
	 * delete all
	 * 
	 * @param id
	 */
	public void deleteAll() {
		Log.i(LOG_TAG, "BuyinDao  delete all from  buyin: ");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete from buyin");
		db.close();
	}

	/**
	 * fetch buyins
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public List<Buyin> getScrollData(int pageNumber, int pageSize) {

		Log.i(LOG_TAG, "BuyinDao  getScrollData " + pageNumber + "," + pageSize);

		List<Buyin> buyins = new ArrayList<Buyin>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID,GoodID,Name,Buyindate,Price,Count,Ispayed,IsReceived,SupplierID,OpratorID,modified from buyin order by modified desc limit ?,?",
						new String[] {
								String.valueOf((pageNumber - 1) * pageSize),
								String.valueOf(pageNumber * pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Buyin buyin = new Buyin();
			buyin.setId(cursor.getString(cursor.getColumnIndex("ID")));
			buyin.setGoodId(cursor.getString(1));
			buyin.setName(cursor.getString(2));
			buyin.setBuyinDate(cursor.getString(3));
			buyin.setPrice(cursor.getFloat(4));
			buyin.setCount(cursor.getInt(5));
			buyin.setPayed(Boolean.getBoolean(cursor.getString(6)));
			buyin.setReceived(Boolean.getBoolean(cursor.getString(7)));
			buyin.setSupplierId(cursor.getString(8));
			buyin.setOpratorId(cursor.getString(9));
			buyin.setModified(cursor.getLong(10));
			buyins.add(buyin);
		}
		cursor.close();
		db.close();
		return buyins;
	}

	/**
	 * fetch buyins
	 * 
	 * @return
	 */
	public List<Buyin> getAll() {

		Log.i(LOG_TAG, "BuyinDao  getAll ");

		List<Buyin> buyins = new ArrayList<Buyin>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID,GoodID,Buyindate,Price,Count,Ispayed,IsReceived,SupplierID,OpratorID,modified from buyin  order by modified desc",
						null);// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Buyin buyin = new Buyin();
			buyin.setId(cursor.getString(cursor.getColumnIndex("ID")));
			buyin.setGoodId(cursor.getString(1));
			buyin.setBuyinDate(cursor.getString(2));
			buyin.setPrice(cursor.getFloat(3));
			buyin.setCount(cursor.getInt(4));
			buyin.setPayed(Boolean.getBoolean(cursor.getString(5)));
			buyin.setReceived(Boolean.getBoolean(cursor.getString(6)));
			buyin.setSupplierId(cursor.getString(7));
			buyin.setOpratorId(cursor.getString(8));
			buyin.setModified(cursor.getLong(9));
			buyins.add(buyin);
		}
		cursor.close();
		db.close();
		return buyins;
	}
	
	/**
	 * 查询所有需要确认的进货
	 * 
	 * @return
	 */
	public ArrayList<Buyin> getAllShouldConfirms() {
		
		Log.i(LOG_TAG, "BuyinDao  getAll should confirm buyins");
		
		ArrayList<Buyin> buyins = new ArrayList<Buyin>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select buyin.ID,buyin.GoodID,buyin.Name,buyin.Buyindate,goods.Price1,buyin.Count,buyin.Ispayed,buyin.IsReceived,buyin.SupplierID,buyin.OpratorID,buyin.modified from buyin,goods where buyin.GoodID=goods.ID and buyin.IsReceived=0 order by buyin.modified desc",
						null);// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Buyin buyin = new Buyin();
			buyin.setId(cursor.getString(cursor.getColumnIndex("ID")));
			buyin.setGoodId(cursor.getString(1));
			buyin.setName(cursor.getString(2));
			buyin.setBuyinDate(cursor.getString(3));
			buyin.setPrice(cursor.getFloat(4));
			buyin.setCount(cursor.getInt(5));
			buyin.setPayed(Boolean.getBoolean(cursor.getString(6)));
			buyin.setReceived(Boolean.getBoolean(cursor.getString(7)));
			buyin.setSupplierId(cursor.getString(8));
			buyin.setOpratorId(cursor.getString(9));
			buyin.setModified(cursor.getLong(10));
			buyins.add(buyin);
		}
		cursor.close();
		db.close();
		return buyins;
	}

	/**
	 * get buyin's count
	 * 
	 * @return
	 */
	public long getCount() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		long count = DatabaseUtils.queryNumEntries(db, TBL_NAME);
		db.close();
		return count;
	}

	/**
	 * 
	 * @param cateId
	 * @param identifier
	 * @param btime
	 * @param etime
	 * @return
	 */
	public int getCount(String cateId, String identifier, long btime, long etime,int IsReceived) {
		Log.i(LOG_TAG, "BuyinDao  getCount " + cateId + "," + identifier);

		int count = 0;
		String sql = "select  count(buyin.ID) from buyin,goods where  buyin.IsReceived="+IsReceived+" and buyin.GoodID=goods.ID ";
		if (identifier.length() != 0) {
			sql += " and buyin.GoodID=" + identifier;
		} 
		
			if (cateId != null) {
				sql += " and goods.CategoryID=" + cateId;
			}
			sql += " and buyin.created >=" + btime + " and buyin.created <=" + etime;
		

		Log.i("sql= ", sql);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);// pageNumber页码
        if(cursor.moveToNext()){
        	count = cursor.getInt(0);
        }
		
		cursor.close();
		db.close();
		return count;

	}

	/**
	 * fetch stock
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public ArrayList<Buyin> getScrollData(String cateId, String identifier,
			long btime, long etime, int pageNumber, int pageSize,int IsReceived) {

		Log.i(LOG_TAG, "BuyinDao  getScrollData " + pageNumber + "," + pageSize);

		String sql = "select buyin.ID,buyin.GoodID,goods.Name,buyin.Buyindate,goods.Price1,buyin.Count,buyin.Ispayed,buyin.IsReceived,buyin.SupplierID,buyin.OpratorID,buyin.modified from buyin,goods where  buyin.IsReceived="+IsReceived+"  and buyin.GoodID=goods.ID ";
		if (identifier.length() != 0) {
			sql += " and buyin.GoodID=" + identifier;
		} 
		
			if (cateId != null) {
				sql += " and buyin.CategoryID=" + cateId;
			}
			sql += " and buyin.created >=" + btime + " and buyin.created <=" + etime;
		

		sql += "  order by buyin.modified desc limit ?,?";

		Log.i("sql= ", sql);
		ArrayList<Buyin> buyins = new ArrayList<Buyin>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql,
				new String[] { String.valueOf((pageNumber - 1) * pageSize),
						String.valueOf(pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Buyin buyin = new Buyin();
			buyin.setId(cursor.getString(cursor.getColumnIndex("ID")));
			buyin.setGoodId(cursor.getString(1));
			buyin.setName(cursor.getString(2));
			buyin.setBuyinDate(cursor.getString(3));
			buyin.setPrice(cursor.getFloat(4));
			buyin.setCount(cursor.getInt(5));
			buyin.setPayed(Boolean.getBoolean(cursor.getString(6)));
			buyin.setReceived(Boolean.getBoolean(cursor.getString(7)));
			buyin.setSupplierId(cursor.getString(8));
			buyin.setOpratorId(cursor.getString(9));
			buyin.setModified(cursor.getLong(10));
			buyins.add(buyin);
		}
		cursor.close();
		db.close();
		return buyins;
	}
	
}
